package DBLayer;
import java.sql.*;
import java.util.ArrayList;
import ModelLayer.Reservation;

public class DBReservation implements IFDBReservation
{
    private Connection con;
    
    public DBReservation()
    {
        con = DBConnection.getInstance().getDBcon();
    }
    
    @Override
    public Reservation findReservation(int resID, boolean retriveAssociation)
    {
        Reservation reservationObj = new Reservation();
        reservationObj = singleWhere("rId = " + resID, retriveAssociation);
        
        return reservationObj;
    }
    
    private Reservation singleWhere(String wClause, boolean retrieveAssociation)
    {	
        ResultSet results;
        Reservation reservationObj = new Reservation();
        String query =  buildQuery(wClause);
        
        try
        {
            Statement stmt = con.createStatement();
            stmt.setQueryTimeout(5);
            results = stmt.executeQuery(query);

            if(results.next())
            {
               reservationObj = buildReservation(results);
            }

            stmt.close();
	}
	catch(Exception e)
        {
        
        }
        
	return reservationObj;
    }
    
    private ArrayList<Reservation> miscWhere(String wClause, boolean retrieveAssociation)
    {
        ResultSet results;
        ArrayList<Reservation> list = new ArrayList<Reservation>();

        String query =  buildQuery(wClause);
        System.out.println(query);
        try
        { 
            Statement stmt = con.createStatement();
            stmt.setQueryTimeout(5);
            results = stmt.executeQuery(query);

            while(results.next())
            {
                 Reservation reservationObj = new Reservation();
                 reservationObj = buildReservation(results);	
                 list.add(reservationObj);
            }

            stmt.close();
        }
        catch(Exception e)
        {

        }

        return list;
    }
    
    private Reservation buildReservation(ResultSet results)
    {
        Reservation reservationObj = new Reservation();
        
        try
        {
            reservationObj.setReservationID(results.getInt(1));
            reservationObj.setCustomerID(null);
            reservationObj.setDate(results.getString(3)); 
            reservationObj.setTimePeriod(results.getString(4));
            reservationObj.setJobType(results.getString(5));
            reservationObj.setEmpID(null); 
            
        }
        catch(Exception e)
        {
            
        }
        
        return reservationObj;
    }
 
    private String buildQuery(String wClause)
    {
        String query="SELECT * FROM Reservation";

        if (wClause.length() > 0)
        {
            query = query + " WHERE " + wClause;
        }
        
        return query; 
    }

    @Override
    public ArrayList getAllReservation(boolean retriveAssociation) 
    {
        ArrayList list = new ArrayList();
        list = miscWhere("", retriveAssociation);
        return list;
    }

    @Override
    public void insertReservation(Reservation reservationObj)
    {
        String query = "INSERT INTO Reservation(date, jobBeskrivelse, timePeriod, cId) VALUES(" +
                "'"+ reservationObj.getDate() + "', " +
                "'"+ reservationObj.getJobType() + "', " +
                "'"+ reservationObj.getTimePeriod() + "', " +
                "'"+ reservationObj.getCustomerID().getId() + "'" +
                ")";
                    
       System.out.println("Update query: " + query);
            try
        { 
            Statement stmt = con.createStatement();
            stmt.setQueryTimeout(5);
            stmt.executeUpdate(query);

            stmt.close();
        }
        catch(Exception ex)
        {

        }
    }

    public void updateReservation (Reservation reservationObj)
    {
        String query="UPDATE Reservation SET "+
                "date ='"+ reservationObj.getDate() + "', " +
                "jobType ='"+ reservationObj.getJobType() + "', " +
                "timePeriod ='"+ reservationObj.getTimePeriod() + "', " +
                "eId ='"+ reservationObj.getEmpID().getEmployeeID() + "', " + // getEmpID skal hedde getEmp istedet
                " WHERE rId = '" + reservationObj.getReservationID() + "'";
        
        try
        { 
            Statement stmt = con.createStatement();
            stmt.setQueryTimeout(5);
            stmt.executeUpdate(query);

            stmt.close();
        }
        catch(Exception ex)
        {

        }
    }

    public void deleteReservation(int resID)
    {
        String query = "DELETE FROM Reservation WHERE resId = '" +
                        resID + "'";

        try
        { 
            Statement stmt = con.createStatement();
            stmt.setQueryTimeout(5);
            stmt.executeUpdate(query);
            stmt.close();
        }
        catch(Exception ex)
        {

        }
    }

    @Override
    public int MaxID()
    {
        String query = "SELECT max(rId) FROM Reservation";
        ResultSet results;
        int id = -1;
        try
        {
            Statement stmt = con.createStatement();
            results = stmt.executeQuery(query);
            if( results.next())
            {
                id = results.getInt(1);
            }
        }
        catch(Exception e)
        {
            System.out.println("Query exception: Error in reading max id: " + e);
        } 
        return id;     
    }
}
